package com.xu.sys.dao.impl;

import com.xu.sys.bean.SysUser;
import com.xu.sys.dao.IUserDao;
import com.xu.sys.utils.MyDbutils;
import com.xu.sys.utils.PageUtils;
import com.xu.sys.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//系统用户Dao的实现类
public class UserDaoImpl implements IUserDao {
    @Override
    public List<SysUser> list(SysUser user) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from sys_user where 1 = 1 ";
        if(user.getRoleId() != null && user.getRoleId() > 0 ){
            //添加
            sql = sql + "and  role_id =" + user.getRoleId();
        }
        try {
            List<SysUser> list = queryRunner.query(sql, new ResultSetHandler<List<SysUser>>() {
                List<SysUser> list = new ArrayList<>();

                @Override
                public List<SysUser> handle(ResultSet resultSet) throws SQLException {
                    while (resultSet.next()) {
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRolename(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        list.add(user); // 把查询的记录封装到了集合容器中
                    }
                    return list;
                }
            });
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //    @param user 查询条件
//* @param pageNum 页码
//* @param pageSize 每页显示的条数
    @Override
    public List<SysUser> listPage(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from sys_user limit ?,?";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            // 需要带条件查询
            sql = "select * from sys_user where username like '%"+pageUtils.getKey()+"%' or nickname like '%"+pageUtils.getKey()+"%' limit ?,?";
        }

        // 计算 分页开始的位置
        int startNo = pageUtils.getStart();
        try {
            List<SysUser> list = queryRunner.query(sql, new ResultSetHandler<List<SysUser>>() {
                List<SysUser> list = new ArrayList<>();
                @Override
                public List<SysUser> handle(ResultSet resultSet) throws SQLException {
                    while (resultSet.next()) {
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRolename(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        list.add(user); // 把查询的记录封装到了集合容器中
                    }
                    return list;
                }
            }, startNo, pageUtils.getPageSize());
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(SysUser user) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "insert into sys_user(username,password,nickname,img,role_id,rolename)values(?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql, user.getUsername(), user.getPassword(), user.getUsername(), user.getImg(),user.getRoleId(),user.getRolename());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    @Override
    public SysUser findById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from sys_user where id = ?";
        try {
            SysUser user = queryRunner.query(sql, new ResultSetHandler<SysUser>() {
                @Override
                public SysUser handle(ResultSet resultSet) throws SQLException {
                    List<SysUser> list = new ArrayList<>();
                    if (resultSet.next()) {
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRolename(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        return user;
                    }
                    return null;
                }
            }, id);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int updteById(SysUser user) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "update sys_user set username=?,password=?,nickname=?,img=?,role_id=?,rolename=? where id = ?";
        try {
            return queryRunner.update(sql, user.getUsername(), user.getPassword(), user.getNickname(), user.getImg(),user.getRoleId(),user.getRolename(), user.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    @Override
    public int deleteById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "delete from sys_user where id = ?";
        try {
            return queryRunner.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    @Override
    public int count(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select count(1) from sys_user ";
        if (StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select count(1) from sys_user  where username like '%"+pageUtils.getKey()+"%' or nickname like '%"+pageUtils.getKey()+"%' ";
        }
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            });
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<SysUser> findByName(String userName) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from sys_user where username = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<SysUser>>() {
                @Override
                public List<SysUser> handle(ResultSet resultSet) throws SQLException {
                    List<SysUser> list = new ArrayList<>();
                    while (resultSet.next()) {
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRolename(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        list.add(user);
                    }
                    return list;
                }
            }, userName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }



    public static void main(String[] args) {
        IUserDao dao = new UserDaoImpl();
    }
}
//